package model;

import java.text.*;
import java.util.*;
import java.sql.*;
import java.sql.Date;

import beans.UserBean;

public class UserDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;  

	public static UserBean login(UserBean bean) {
		Statement stmt = null;    

		String username = bean.getEmail();    
		String password = bean.getPassword();   
		String searchQuery =
				"select * from users where email='"
						+ username + "'";

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			if (rs.next() == true)
				if (BCrypt.checkpw(password, rs.getString("password"))) {
					String pulledemail = rs.getString(1);
					String pulledpassword = password;
					String pulledname = rs.getString(3);
					boolean pulledgendermale = rs.getBoolean(4);
					Date pulledbirthdate = rs.getDate(5);
					String pulledphotoURL = rs.getString(6);
					int pulledphone = rs.getInt(7);
					boolean pulledsetting1 = rs.getBoolean(8);
					int pulledsetting2 = rs.getInt(9);
					Timestamp pulledlastlogin = rs.getTimestamp(10);
					Timestamp pulledregdate = rs.getTimestamp(11);
					boolean pulledadminflag = rs.getBoolean(12);
					String pulledcity = rs.getString(13);
					String pulledregion = rs.getString(14);
					String pulledcountry = rs.getString(15);
					int pulledpoints = rs.getInt("points");

					java.util.Date date= new java.util.Date();
					Timestamp lastlogin = new Timestamp(date.getTime());

					bean.setEmail(pulledemail);
					bean.setPassword(pulledpassword);
					bean.setName(pulledname);
					bean.setGenderMale(pulledgendermale);
					bean.setBirthDate(pulledbirthdate);
					bean.setPhotoURL(pulledphotoURL);
					bean.setPhone(pulledphone);
					bean.setSetting1(pulledsetting1);
					bean.setSetting2(pulledsetting2);
					bean.setLastLogin(lastlogin);
					bean.setRegistration(pulledregdate);
					bean.setAdmin(pulledadminflag);
					bean.setCity(pulledcity);
					bean.setRegion(pulledregion);
					bean.setCountry(pulledcountry);
					bean.setValid(true);
					bean.setPoints(pulledpoints);

					stmt.executeQuery("update users set lastlogintimestamp = '"+ lastlogin +"'where email='"
							+ username
							+ "' AND password='"
							+ password
							+ "'");
				}

				else {
					bean.setValid(false);
				}
			else
				bean.setValid(false);

		} 

		catch (Exception ex) {
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return bean;
	}	


	public static UserBean signup(UserBean newUser) {
		Statement stmt = null;    

		java.util.Date date= new java.util.Date();
		Timestamp lastlogin = new Timestamp(date.getTime());

		String hashedPassword = BCrypt.hashpw(newUser.getPassword(), BCrypt.gensalt());
		String insertQuery = "insert into users values ('" +
				newUser.getEmail() + "', '" +
				hashedPassword + "', '" +
				newUser.getName() + "', '" +
				newUser.getGenderMale() + "', '" +
				newUser.getBirthDate() + "', 'http://placehold.it/290x290&text=Profile+Pic', NULL, TRUE, 30, '" +
				lastlogin + "', '" + lastlogin + "', FALSE, '" +
				newUser.getCity() + "', '" +
				newUser.getRegion() + "', '" +
				newUser.getCountry() + "');";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeQuery(insertQuery);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return login(newUser);

	}

	public static UserBean visit(UserBean bean) {

		Statement stmt = null;    

		String username = bean.getEmail();    

		String searchQuery =
				"select * from users where email='"
						+ username
						+ "'";

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			boolean more = rs.next();

			if (!more) {
				bean.setValid(false);
			} 

			else if (more) {
				String pulledemail = rs.getString(1);
				String pulledname = rs.getString(3);
				boolean pulledgendermale = rs.getBoolean(4);
				Date pulledbirthdate = rs.getDate(5);
				String pulledphotoURL = rs.getString(6);
				int pulledphone = rs.getInt(7);
				boolean pulledsetting1 = rs.getBoolean(8);
				int pulledsetting2 = rs.getInt(9);
				Timestamp pulledlastlogin = rs.getTimestamp(10);
				Timestamp pulledregdate = rs.getTimestamp(11);
				boolean pulledadminflag = rs.getBoolean(12);
				String pulledcity = rs.getString(13);
				String pulledregion = rs.getString(14);
				String pulledcountry = rs.getString(15);
				int pulledpoints = rs.getInt("points");

				bean.setEmail(pulledemail);
				bean.setName(pulledname);
				bean.setGenderMale(pulledgendermale);
				bean.setBirthDate(pulledbirthdate);
				bean.setPhotoURL(pulledphotoURL);
				bean.setPhone(pulledphone);
				bean.setSetting1(pulledsetting1);
				bean.setSetting2(pulledsetting2);
				bean.setLastLogin(pulledlastlogin);
				bean.setRegistration(pulledregdate);
				bean.setAdmin(pulledadminflag);
				bean.setCity(pulledcity);
				bean.setRegion(pulledregion);
				bean.setCountry(pulledcountry);
				bean.setValid(true);
				bean.setPoints(pulledpoints);
			}
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return bean;
	}

	public static ArrayList<UserBean> search(UserBean bean) {

		Statement stmt = null; 
		String email = bean.getEmail();  
		String name = bean.getName();
		String country = bean.getCountry();
		String city = bean.getCity();

		String searchQuery = "select email,name,city,country,lastlogintimestamp,registrationtimestamp from users where email ilike '%" + email + "%' and name ilike '%" + name + "%' and city ilike '%" + city + "%' and country ilike '%" + country + "%' order by name ASC";

		ArrayList<UserBean> searchResults = new ArrayList<UserBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        

			while (rs.next()) {

				UserBean user = new UserBean();
				String pulledemail = rs.getString(1);
				String pulledname = rs.getString(2);
				Timestamp pulledlastlogin = rs.getTimestamp(5);
				Timestamp pulledregdate = rs.getTimestamp(6);
				String pulledcity = rs.getString(3);
				String pulledcountry = rs.getString(4);

				user.setEmail(pulledemail);
				user.setName(pulledname);
				user.setLastLogin(pulledlastlogin);
				user.setRegistration(pulledregdate);
				user.setCity(pulledcity);
				user.setCountry(pulledcountry);
				searchResults.add(user);

			}
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return searchResults;
	}

	public static void updatePhotoURL(UserBean user) {
		Statement stmt = null;    

		String updateQuery = "update users set photourl='" + user.getPhotoURL() + "' where email='" + user.getEmail() + "'";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(updateQuery);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}

	public static String getName(UserBean user) {
		Statement stmt = null;
		String nameQuery = "select name from users where email='" + user.getEmail() + "'";
		String name = null;
		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(nameQuery);	        
			while (rs.next()) {
				name = rs.getString("name");
			}
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return name;
	}
}